package com.itheima1.dao;

import com.itheima1.domain.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class StudentDaoImpl implements StudentDao {
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/db14", "root", "chenbo123");

            //获取执行者对象
            stat = con.createStatement();

//            执行sql语句  并接收返回结果
            String sql = "SELECT * FROM student";
            rs = stat.executeQuery(sql);

            //处理结果集
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                //封装学生对象
                Student stu = new Student(sid, name, age, birthday);
                list.add(stu);

            }

        } catch (Exception e) {
            e.printStackTrace();

        }finally {
            //释放资源
            if (con!=null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }

    @Override
    public Student findById(Integer id) {
//创建学生对象用来封装学生信息
        Student stu = new Student();
        Connection con = null;
        Statement stat = null;
        ResultSet rs = null;

        // 获取异常
        try {
// 注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //获取数据库连接
            con = DriverManager.getConnection("jdbc:mysql://192.168.23.129/db14", "root", "chenbo123");
//          获取执行对象
            stat = con.createStatement();

            //执行sql语句
            String sql = "SELECT * FROM student WHERE sid='"+id+"'";
             rs=stat.executeQuery(sql);

            // 处理结果集
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date birthday = rs.getDate("birthday");

                // 封装学生对象
                stu.setSid(sid);
                stu.setName(name);
                stu.setAge(age);
                stu.setBirthday(birthday);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //释放资源
            //释放资源
            if (con!=null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return stu;
    }

    @Override
    public int insert(Student stu) {
        Connection con = null;
        Statement stat = null;
        // 定义返回参数
        int result = 0;

        // 获取异常信息
        try {
        // 注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //获取数据库连接
           con =DriverManager.getConnection("jdbc:mysql://192.168.23.129/db14", "root", "chenbo123");

           // 获取执行者对象
            stat = con.createStatement();

            //执行sql语句  接收返回结果
            // 格式化日期格式
            Date b = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(b);
            String sql = "INSERT INTO student VALUES ('" + stu.getSid() + "','" + stu.getName() + "','" + stu.getAge() + "','" + birthday + "')";

            //返回一个int类型的数 如果此数=0是未修改行数 =1修改了一行

            result = stat.executeUpdate(sql);


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //释放资源
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    @Override
    public int update(Student stu) {
//创建
        Connection con = null;
        Statement stat = null;
        int result = 0;
        //获取异常
        try {
// 注册驱动
            Class.forName("com.mysql.jdbc.Driver");


            //获取数据库连接
            con=DriverManager.getConnection("jdbc:mysql://192.168.23.129/db14", "root", "chenbo123");

            //获取执行者对象
            stat=con.createStatement();

            //执行sql语句 并且返回接收的结果集
            // 格式化Date类型
            Date d = stu.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String birthday = sdf.format(d);

            String sql = "UPDATE student SET  sid='" + stu.getSid() + "',name='" + stu.getName() + "',age='" + stu.getAge() + "',birthday='" + birthday + "' WHERE sid='"+stu.getSid()+"'";

            result = stat.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();

        }finally {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    @Override
    public int delete(Integer id) {
        Connection con = null;
        Statement stat = null;
        int result = 0;
        //异常
        try {
//注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            //获取数据库连接

            con=DriverManager.getConnection("jdbc:mysql://192.168.23.129/db14", "root", "chenbo123");
            //获取执行者对象
            stat = con.createStatement();

            //执行sql语句
            String sql = "DELETE FROM student WHERE sid='" + id + "'";

            result=stat.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //释放资源
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

}
